package com.boot.management_boot.mapper;

import com.boot.management_boot.pojo.Admin_allinfo;
import com.boot.management_boot.pojo.User;
import com.boot.management_boot.pojo.User_info;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
@Repository
public interface BootMapper {
    //登录
    @Select("select * from user where username=#{username} and password=#{password} and code != 3")
    User login(@Param("username")String username,@Param("password") String password);

    //查询用户的数据信息
    @Select("SELECT info_time,info_liushui,info_shouyi FROM user_info i INNER JOIN user u ON i.user_id = u.id  WHERE u.id = #{uid} ORDER BY info_time")
    List<User_info> queryuserInfo(@Param("uid")int uid);

    //修改密码
    @Update("UPDATE user set password = #{password} WHERE username = #{username}")
    int update_user_password(@Param("username")String username, @Param("password")String password);


    //判断用户名是否存在
    @Select("select COUNT(username) from user where username= #{username}")
    int query_user_name(@Param("username")String username);

    //添加用户 admin
    @Insert("INSERT into user (username,password,edu,add_time) VALUES (#{username},#{password},#{edu},NOW())")
    int add_user(String username,String password,String edu);

//    //查询所有用户总计的数据
//    @Select("SELECT username,SUM(i.info_liushui) liushui,SUM(i.info_shouyi) shouyi from user u INNER JOIN user_info i on u.id = i.user_id GROUP BY username")
//    List<Map> query_all_info_user();

    //查询用户当前月的数据信息
    @Select("SELECT IFNULL(info_liushui,'') info_liushui,IFNULL(info_shouyi,'') info_shouyi,datelist info_time FROM (select info_liushui,info_shouyi,info_time FROM user_info WHERE user_id = #{uid}) u RIGHT JOIN calendar c ON u.info_time = c.datelist WHERE DATE_FORMAT(datelist, '%Y-%m-%d') BETWEEN #{time0} AND #{time1} GROUP BY  datelist")
    List<User_info> queryuserInfo_m(@Param("uid")int uid, @Param("time0")String time0, @Param("time1")String time1);

    //查询管理员界面所有用户信息
    @Select("select id,IFNULL(SUM(info_liushui),0) info_liushui,IFNULL(SUM(info_shouyi),0) info_shouyi,username,DATE_FORMAT(add_time,'%Y-%m-%d %H:%i:%s') add_time,edu from user u LEFT JOIN user_info i on u.id = i.user_id WHERE u.code = 0 GROUP BY u.id ORDER BY add_time DESC")
    List<Map> admin_queryuserallinfo();

    @Update("UPDATE user SET password = #{password} WHERE username = #{username} and id = #{id}")
    //管理员修改用户密码
    int admin_updatepassword(String username,String password,int id);

    @Update("UPDATE user SET code = '3' WHERE id = #{id}")
    //删除此账户  将此账户code修改为3
    int admin_deluser(int id);

    //判断当前日期是否有数据
    @Select("SELECT * from user_info WHERE user_id= #{id} and DATE_FORMAT(info_time,'%Y-%m-%d')=#{info_time}")
    User_info query_info_f_t(int id,String info_time);

    @Update("UPDATE user_info SET info_liushui = #{info_liushui},update_time = NOW() WHERE info_time=#{info_time} and user_id = #{id}")
    //存在数据修改
    int update_info_liushui(int id,String info_time,int info_liushui);

    @Update("UPDATE user_info SET info_shouyi = #{info_shouyi},update_time = NOW() WHERE info_time=#{info_time} and user_id = #{id}")
        //存在数据修改
    int update_info_shouyi(int id,String info_time,int info_shouyi);

    @Insert("INSERT INTO user_info (info_time,info_shouyi,create_time,user_id) VALUES (#{info_time},#{info_shouyi},NOW(),#{id})")
    //不存在就创建
    int insert_info_shouyi(int id,String info_time,int info_shouyi);
    @Insert("INSERT INTO user_info (info_time,info_liushui,create_time,user_id) VALUES (#{info_time},#{info_liushui},NOW(),#{id})")
        //不存在就创建
    int insert_info_liushui(int id,String info_time,int info_liushui);

    //删除账户
    @Delete("DELETE FROM user WHERE id = #{id}")
    int del_user(int id);

    //删除此账户下边的所有数据
    @Delete("DELETE FROM user_info WHERE user_id = #{user_id}")
    int del_userinfo(int user_id);

    //删除时间区间内此账户下边的数据
    @Delete("DELETE FROM user_info WHERE user_id = #{user_id} and DATE_FORMAT(info_time, '%Y-%m-%d') BETWEEN #{time} and #{time1} ")
    int del_userinfo_time(int user_id,String time,String time1);

    @Update("UPDATE user SET edu = #{edu} WHERE username = #{username} and id = #{id}")
        //管理员修改用户的额度
    int admin_updateedu(String edu,String username,int id);

    //超管添加管理员
    @Insert("INSERT INTO user (username,password,add_time,code) VALUES (#{username},#{password},NOW(),'1')")
    int add_admin_user(String username,String password);

    //超管查询所有管理员信息
    @Select("select id,IFNULL(SUM(info_liushui),0) info_liushui,IFNULL(SUM(info_shouyi),0) info_shouyi,username,DATE_FORMAT(add_time,'%Y-%m-%d %H:%i:%s') add_time,edu from user u LEFT JOIN user_info i on u.id = i.user_id WHERE u.code = 1 and edu != '1433223' GROUP BY u.id ORDER BY add_time DESC")
    List<Map> super_admin_user();

    //查询当前用户的所有数据
    @Select("select * from user where username = #{username}")
    Map query_user(String username);

}
